<!DOCTYPE html>
<html lang="en">
	<head>
		<meta charset="UTF-8">
		<title>Excel批量处理</title>
		<script src="https://cdn.bootcss.com/jquery/3.2.1/jquery.js"></script>
		<script src="https://cdn.bootcss.com/xlsx/0.11.5/xlsx.core.min.js"></script>
	</head>
	<body>
		<input type="file" id="excel-file">
		<p>结果</p>
		<pre id="code"></pre>
		<!-- 		<textarea id="area" rows="100" cols="100"></textarea>
 -->
		<script>
			class Scheam{
				
				constructor() {
				    this.databases = new Map();
				}
				/**
				 * 增加表字段
				 * @param {Object} table 表名
				 * @param {Object} column 字段名
				 */
				addColumn(tableName,columnName){
					let t = this.getTable(tableName);
					if(!t){
						t = new Array();
						this.databases.set(tableName,t);
					}
					t.push(columnName);
				}
				
				getTable(name){
					return this.databases.get(name);
				}
				
				exist(tableName,columnName){
					let t = this.getTable(tableName);
					for (var i = 0; i < t.length; i++) {
						let c = t[i];
						if(c == columnName){
							return true;
						}
					}
					return false;
				}
			}
			var printFlag = false;
			var scheam = new Scheam();
			//给input标签绑定change事件，一上传选中的.xls文件就会触发该函数
			$('#excel-file').change(function(e) {
				var files = e.target.files;
				var fileReader = new FileReader();
				fileReader.onload = function(ev) {
					try {
						var data = ev.target.result
						var workbook = XLSX.read(data, {
							type: 'binary'
						}) // 以二进制流方式读取得到整份excel表格对象
						var persons = []; // 存储获取到的数据
					} catch (e) {
						console.log('文件类型不正确');
						return;
					}
					// 表格的表格范围，可用于判断表头是否数量是否正确
					var fromTo = '';
					var sql = "";
					var i = 0;
					// 遍历每张表读取
					for (var sheet in workbook.Sheets) {
						if (i > 0) {
							if (workbook.Sheets.hasOwnProperty(sheet)) {
								fromTo = workbook.Sheets[sheet]['!ref'];
								let sht = workbook.Sheets[sheet];

								window.sht = sht;
								//persons = persons.concat(XLSX.utils.sheet_to_json(workbook.Sheets[sheet]));
								let tableComment = sht.B2.v; //表注释
								let tableName = sht.B1.v; //表名
								//根据表名过滤
								// let keywordsArr = [];
								// let exclude = false;
								// for (var i = 0; i < keywordsArr.length; i++) {
								// 	let keywords = keywordsArr[i];
								// 	if(tableName==keywords){
								// 		exclude = true;
								// 	}
								// }
								// if(!exclude){
								// 	continue;
								// }
					
								//$("#code").append("-- ------------"+tableName+"("+tableComment+")----------<br/>")
								//生成表注释sql
								//let tableSql = "comment on table REDSUN."+tableName+" is '"+tableComment+"';";
								//生成sql
								let rowSql = buildDDLSql(tableName, tableComment, sht)
								//console.log(rowSql)
								//$("#code").append(rowSql + "<br/>");
							}
						}
						i++;
					}
					printFlag = true;
					
				};
				// 以二进制方式打开文件
				fileReader.readAsBinaryString(files[0]);
			});

			function buildDDLSql(tableName, tableComment, sheet) {
				//sht.B1.v;
				let index = 4;
				let sqlRes = "";
				let table = new Table(tableName, tableComment);
				do {
					let columnName = "",
						columnComment = "",
						columnLength = 0,
						columnForeignTable = "",
						isPrimaryKey = false,
						type = "";
					let end = false;
					//字段
					let columnNameRc = "B" + index;
					let columnNameCell = sheet[columnNameRc];
					if (columnNameCell) {
						columnName = columnNameCell.v;
					} else {
						end = true;
					}
					//字段注释
					let columnCommentRc = "I" + index;
					let columnCommentCell = sheet[columnCommentRc];
					if (columnCommentCell) {
						columnComment = columnCommentCell.v;
					}
					
					//字段长度
					let columnLengthCellIndex = "G" + index;
					let comlumnLengthCell = sheet[columnLengthCellIndex];
					if (comlumnLengthCell) {
						columnLength = comlumnLengthCell.v;
					}
					//外键表
					let columnForeignRc = "J" + index;
					let columnForeignCell = sht[columnForeignRc];
					if (columnForeignCell) {
						columnForeignTable = columnForeignCell.v;
						//打印外键语句
						if(printFlag&&scheam.exist(columnForeignTable,columnName)){
							let script = `alter table ${tableName} add constraint FK_ID_${tableName} foreign key(${columnName}) REFERENCES ${columnForeignTable}(${columnName});`;
							$("#code").append(script + "<br/>");
						}
					}
					//字段类型
					type = getXlsCellValueFormSheet(sht, "F", index);
					//将类型转换成MySQL中的类型
					type = type == "QUAN" ? "int" : "varchar";

					//是否主键
					isPrimaryKey = getXlsCellValueFormSheet(sht, "C", index);
					if(columnName&&columnName.length>0&&"?"!=columnComment&&columnName.indexOf(".")==-1){
						table.addColumn(columnName, type, columnLength, isPrimaryKey == "X", columnComment);
					}
					if (end) {
						return table.toAlterPrimaryScript();
					}
					
					index++;
				} while (true);
			}

			function getXlsCellValueFormSheet(sheet, col, row) {
				let rc = col + row;
				let cell = sheet[rc];
				if (cell) {
					return cell.v;
				}
				return;
			}
			class Table {

				constructor(name, comment) {
					this.name = name;
					this.comment = comment;
					this.columnIndex = 0;
					this.alreadyHasPrimaryKey = false;
					this.script = "create table " + name + "(\r\n";
					this.primaryKeyScript="alter table "+name+" add constraint pk_tab_"+name+" primary key (";
				}

				addColumn(name, type, length, primaryKey, columnComment) {
					scheam.addColumn(this.name,name);
					if(this.columnIndex>0){
						this.script += ",\r\n";
					}
					this.script += name + " " + type + "(" + length + ")";
					if(primaryKey){
						this.primaryKeyScript+=name+","
					}
					if (primaryKey&&!this.alreadyHasPrimaryKey) {
						this.script += " primary key"
						this.alreadyHasPrimaryKey=true;
					}
					if (columnComment) {
						this.script += " COMMENT \"" + columnComment + "\"";
					}
					this.columnIndex++;
				}
				
				toAlterPrimaryScript(){
					return this.primaryKeyScript+=");";
				}
				
				toScript() {
					//去除最有一个逗号
					var reg = /,$/gi;
					this.script = this.script.replace(reg, "");

					return this.script + ")ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COMMENT='" + this.comment + "';";
				}
			}
			
			
		</script>
	</body>
</html>
